/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sql;

/**
 *
 * @author Alexx
 */
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateTables {
    
    public static boolean creator() throws SQLException
    {
        createRoll();
        createUsers();
        createPersons();
        createTelType();
        createAdrType();
        createCountry();
        createCity();
        createStreet();
        createPhone();
        createAdr();
       
      //  firstStart();
        
        return true;
    }
    
    public static boolean creator2() throws SQLException
    {
      creator();
      firstStart();
      return true;
    }
    
    public static void firstStart() throws SQLException
    {
        createOneUsers();
        createOneRoll("1", "Admin");
        createOneRoll("2", "SUser");
        createOneRoll("3", "User");
        createOnePerson("1");
        createOnePerson("2");
        createOnePerson("3");
        createOneTelType("1", "Home");
        createOneTelType("2", "Mob");
        createOneAdrType("1", "Work-a");
        createOneAdrType("2", "Propiska");
        createOneCountry("1", "Uk");
        createOneCountry("2", "Cz");
        createOneCity("1", "Dnepr", "1");
        createOneCity("2", "Praha", "2");
        createOneCity("3", "Kiev", "1");
        createOneStreet("1", "Kirova", "1");
        createOneStreet("2", "Plosha", "2");
        createOnePhone("1", "1", "1");
        createOnePhone("2", "2", "2");
        createOnePhone("3", "2", "1");
        createOneAdrr("1", "1", "1");
        createOneAdrr("2", "2", "2");
        createOneAdrr("3", "2", "1");
        
    }
    
    private static boolean createUsers()
    {
          try {
            Statement statement = Conector.conect();
            String query = "CREATE TABLE user (id IDENTITY , login VARCHAR(20) NOT NULL, pass VARCHAR(20) NOT NULL, rollid VARCHAR(20) NOT NULL)";
            try {
                statement.executeUpdate(query);
            } catch (SQLException e) {
                // если таблица уже существует, будет исключение, игнорируем его.
            }
            
            query = "SHUTDOWN";
            statement.execute(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return true;
    }
    
    
    private static boolean createPersons()
    {
     try {
            Statement statement = Conector.conect();
            String query = "CREATE TABLE persone (id IDENTITY, fname VARCHAR(20) NOT NULL, lname VARCHAR(20) NOT NULL, bd VARCHAR(20) NOT NULL, uid VARCHAR(20) NOT NULL)";
            try {
                statement.executeUpdate(query);
            } catch (SQLException e) {
                // если таблица уже существует, будет исключение, игнорируем его.
            }
            query = "SHUTDOWN";
            statement.execute(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }   
        return true;
    }
    
    private static boolean createTelType()
    {
     try {
            Statement statement = Conector.conect();
            String query = "CREATE TABLE teltype (id IDENTITY, telcat VARCHAR(20) NOT NULL)";
            try {
                statement.executeUpdate(query);
            } catch (SQLException e) {
                // если таблица уже существует, будет исключение, игнорируем его.
            }
            query = "SHUTDOWN";
            statement.execute(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }   
        return true;
    }
    
     private static boolean createAdrType()
    {
     try {
            Statement statement = Conector.conect();
            String query = "CREATE TABLE adrtype (id IDENTITY, adrcat VARCHAR(20) NOT NULL)";
            try {
                statement.executeUpdate(query);
            } catch (SQLException e) {
                // если таблица уже существует, будет исключение, игнорируем его.
            }
            query = "SHUTDOWN";
            statement.execute(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }   
        return true;
    }
    
      private static boolean createCountry() {
        try {
            Statement statement = Conector.conect();
            String query = "CREATE TABLE country (id IDENTITY, ncountry VARCHAR(20) NOT NULL)";
            try {
                statement.executeUpdate(query);
            } catch (SQLException e) {
                // если таблица уже существует, будет исключение, игнорируем его.
            }
            query = "SHUTDOWN";
            statement.execute(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }
      
      private static boolean createCity() {
        try {
            Statement statement = Conector.conect();
            String query = "CREATE TABLE city (id IDENTITY, ncity VARCHAR(20) NOT NULL, countid VARCHAR(20) NOT NULL)";
            try {
                statement.executeUpdate(query);
            } catch (SQLException e) {
                // если таблица уже существует, будет исключение, игнорируем его.
            }
            query = "SHUTDOWN";
            statement.execute(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }
      
    private static boolean createStreet() {
        try {
            Statement statement = Conector.conect();
            String query = "CREATE TABLE street (id IDENTITY, nstreet VARCHAR(20) NOT NULL, cityid VARCHAR(20) NOT NULL)";
            try {
                statement.executeUpdate(query);
            } catch (SQLException e) {
                // если таблица уже существует, будет исключение, игнорируем его.
            }
            query = "SHUTDOWN";
            statement.execute(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }
    
     private static boolean createRoll() {
        try {
            Statement statement = Conector.conect();
            String query = "CREATE TABLE roll (id IDENTITY, nroll VARCHAR(20) NOT NULL)";
            try {
                statement.executeUpdate(query);
            } catch (SQLException e) {
                // если таблица уже существует, будет исключение, игнорируем его.
            }
            query = "SHUTDOWN";
            statement.execute(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }
     
     private static boolean createPhone() {
        try {
            Statement statement = Conector.conect();
            String query = "CREATE TABLE phone (id IDENTITY, nphone VARCHAR(20) NOT NULL, teltypeid VARCHAR(20) NOT NULL, personid VARCHAR(20) NOT NULL)";
            try {
                statement.executeUpdate(query);
            } catch (SQLException e) {
                // если таблица уже существует, будет исключение, игнорируем его.
            }
            query = "SHUTDOWN";
            statement.execute(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }
     
     private static boolean createAdr() {
        try {
            Statement statement = Conector.conect();
            String query = "CREATE TABLE adres (id IDENTITY, country VARCHAR(20) NOT NULL, city VARCHAR(20) NOT NULL, "
                    + "street VARCHAR(20) NOT NULL, add VARCHAR(20) NOT NULL, typeid VARCHAR(20) NOT NULL, personeid VARCHAR(20) NOT NULL)";
            try {
                statement.executeUpdate(query);
            } catch (SQLException e) {
                // если таблица уже существует, будет исключение, игнорируем его.
            }
            query = "SHUTDOWN";
            statement.execute(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }
    //////////////////////////
     
    private static boolean createOneAdrr(String num, String cat, String pid) throws SQLException
    {
 
            Statement statement = Conector.conect();
            String query = "INSERT INTO adres (id, country, city, street, add, typeid, personeid) VALUES ('" + num + "', '1', '1', '1', 'flat 30', '" + cat + "', '" + pid + "')";
            statement.executeUpdate(query);
            
            query = "SHUTDOWN";
            statement.execute(query);
     
        return true;
    } 
     
    private static boolean createOnePhone(String num, String cat, String pid) throws SQLException
    {
 
            Statement statement = Conector.conect();
            String query = "INSERT INTO phone (id, nphone, teltypeid, personid) VALUES ('" + num + "', '777-77-77', '" + cat + "', '" + pid + "')";
            statement.executeUpdate(query);
            
            query = "SHUTDOWN";
            statement.execute(query);
     
        return true;
    }
     
    private static boolean createOneRoll(String num, String cat) throws SQLException
    {
 
            Statement statement = Conector.conect();
            String query = "INSERT INTO roll (id, nroll) VALUES ('" + num + "', '" + cat + "')";
            statement.executeUpdate(query);
            
            query = "SHUTDOWN";
            statement.execute(query);
     
        return true;
    } 
     
    private static boolean createOneStreet(String num, String cat, String id) throws SQLException
    {
 
            Statement statement = Conector.conect();
            String query = "INSERT INTO street (id, nstreet, cityid) VALUES ('" + num + "', '" + cat + "', '" + id + "')";
            statement.executeUpdate(query);
            
            query = "SHUTDOWN";
            statement.execute(query);
     
        return true;
    }
    
    private static boolean createOneCity(String num, String cat, String id) throws SQLException
    {
 
            Statement statement = Conector.conect();
            String query = "INSERT INTO city (id, ncity, countid) VALUES ('" + num + "', '" + cat + "', '" + id + "')";
            statement.executeUpdate(query);
            
            query = "SHUTDOWN";
            statement.execute(query);
     
        return true;
    }
      
    private static boolean createOneCountry(String num, String cat) throws SQLException
    {
 
            Statement statement = Conector.conect();
            String query = "INSERT INTO country (id, ncountry) VALUES ('" + num + "', '" + cat + "')";
            statement.executeUpdate(query);
            
            query = "SHUTDOWN";
            statement.execute(query);
     
        return true;
    }
      
    private static boolean createOneAdrType(String num, String cat) throws SQLException
    {
 
            Statement statement = Conector.conect();
            String query = "INSERT INTO adrtype (id, adrcat) VALUES ('" + num + "', '" + cat + "')";
            statement.executeUpdate(query);
            
            query = "SHUTDOWN";
            statement.execute(query);
     
        return true;
    }
     
    private static boolean createOnePerson(String num) throws SQLException
    {
 
            Statement statement = Conector.conect();
            String query = "INSERT INTO persone (id, fname, lname, bd, uid) VALUES ('" + num + "', 'Peta2', 'Petrov2', '11-12-1987', '2')";
            statement.executeUpdate(query);
            
            query = "SHUTDOWN";
            statement.execute(query);
     
        return true;
    }
    
    private static boolean createOneTelType(String num, String cat) throws SQLException
    {
 
            Statement statement = Conector.conect();
            String query = "INSERT INTO teltype (id, telcat) VALUES ('" + num + "', '" + cat + "')";
            statement.executeUpdate(query);
            
            query = "SHUTDOWN";
            statement.execute(query);
     
        return true;
    }
    
    private static boolean createOneUsers()
    {
      try {
            Statement statement = Conector.conect();
      
            try {
                String query = "INSERT INTO user (id, login, pass, rollid) VALUES ('1', 'Admin', 'Admin', '1')";
                statement.executeUpdate(query);
                
                query = "INSERT INTO user (id, login, pass, rollid) VALUES ('2', 'SUser', 'SUser', '2')";
                statement.executeUpdate(query);
                
                query = "INSERT INTO user (id, login, pass, rollid) VALUES ('3', 'User', 'User', '3')";
                statement.executeUpdate(query);
            } catch (SQLException e) {
                // если таблица уже существует, будет исключение, игнорируем его.
            }
            String query = "SHUTDOWN";
            statement.execute(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } 
      return true;
    }
    
}
